package com.food.dao;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.food.dao.in.MemberDAO;
import com.food.model.Member;

public class MemberDAOJdbc implements MemberDAO {

//	private static final String URL = "jdbc:sqlserver://192.168.34.108:1433;database=PJDB";
//	private static final String USER = "sa";
//	private static final String PASSWORD = "sa123456";
	
	private DataSource ds = null;
	public MemberDAOJdbc() {
		try {
			Context ctx = new InitialContext();
			this.ds = (DataSource) ctx.lookup("java:comp/env/jdbc/food");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	private static final String INSERT = "insert into member (mbname, mbsex, mbidentity, mbbirth, mbnick, mbemail, mbtel,mbaddr, mbaccount, mbpassword, mbimg) values (?,?,?,?,?,?,?,?,?,?,?)";

	@Override
	public void insertMember(Member bean,InputStream is, int size) {

		Connection conn = null;
		PreparedStatement stmt = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(INSERT);

			stmt.setString(1, bean.getMbname());
			stmt.setString(2, bean.getMbsex());
			stmt.setString(3, bean.getMbidentity());
			stmt.setDate(4, bean.getMbbirth());
			stmt.setString(5, bean.getMbnick());
			stmt.setString(6, bean.getMbemail());
			stmt.setString(7, bean.getMbtel());
			stmt.setString(8, bean.getMbaddr());
			stmt.setString(9, bean.getMbaccount());
			stmt.setBytes(10, bean.getMbpassword());
			stmt.setBinaryStream(11,  is, size);

			int i = stmt.executeUpdate();
			if (i == 1) {
				this.findMember(bean.getMbid());
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private static final String UPDATE = "update member set mbname=?, mbsex=?, mbidentity=?, mbbirth=?, mbnick=?, mbemail=?, mbtel=?,mbaddr=?, mbaccount=?, mbimg =? where mbid=?";

	@Override
	public void updateMember(String mbname, String mbsex, String mbidentity,
			Date mbbirth, String mbnick, String mbemail, String mbtel,
			String mbaddr, String mbaccount, byte[] mbimg,
			int mbid,InputStream is, int size) {

		Member result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(UPDATE);

			stmt.setString(1, mbname);
			stmt.setString(2, mbsex);
			stmt.setString(3, mbidentity);
			stmt.setDate(4, mbbirth);
			stmt.setString(5, mbnick);
			stmt.setString(6, mbemail);
			stmt.setString(7, mbtel);
			stmt.setString(8, mbaddr);
			stmt.setString(9, mbaccount);
		//	stmt.setBytes(10, mbpassword);
			stmt.setBinaryStream(10, is,size);
			stmt.setInt(11, mbid);

			int i = stmt.executeUpdate();
			if (i == 1) {
				result = new Member();
				result.setMbname(mbname);
				result.setMbsex(mbsex);
				result.setMbidentity(mbidentity);
				result.setMbbirth(mbbirth);
				result.setMbnick(mbnick);
				result.setMbemail(mbemail);
				result.setMbtel(mbtel);
				result.setMbaddr(mbaddr);
				result.setMbaccount(mbaccount);
		//		result.setMbpassword(mbpassword);
				result.setMbimg(mbimg);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	private static final String SELECT_BY_ID = "select mbid,mbname ,mbsex, mbidentity, mbbirth, mbnick, mbemail, mbtel,mbaddr, mbaccount, mbpassword, mbimg from member where mbid = ?";

	@Override
	public Member findMember(int mbid) {
		Member result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT_BY_ID);
			stmt.setInt(1, mbid);
			rset = stmt.executeQuery();
			if (rset.next()) {
				result = new Member();
				result.setMbid(rset.getInt("mbid"));
				result.setMbname(rset.getString("mbname"));
				result.setMbsex(rset.getString("mbsex"));
				result.setMbidentity(rset.getString("mbidentity"));
				result.setMbbirth(rset.getDate("mbbirth"));
				result.setMbnick(rset.getString("mbnick"));
				result.setMbemail(rset.getString("mbemail"));
				result.setMbtel(rset.getString("mbtel"));
				result.setMbaddr(rset.getString("mbaddr"));
				result.setMbaccount(rset.getString("mbaccount"));
				result.setMbpassword(rset.getBytes("mbpassword"));
				result.setMbimg(rset.getBytes("mbimg"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rset != null) {
				try {
					rset.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	
	private static final String SELECT_BY_ACCOUNT = "select mbid,mbname ,mbsex, mbidentity, mbbirth, mbnick, mbemail, mbtel,mbaddr, mbaccount, mbpassword, mbimg from member where mbaccount = ?";
	public Member findMemberByAccount (String mbaccount){
		Member result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT_BY_ACCOUNT);
			stmt.setString(1, mbaccount);
			rset = stmt.executeQuery();
			if (rset.next()) {
				result = new Member();
				result.setMbid(rset.getInt("mbid"));
				result.setMbname(rset.getString("mbname"));
				result.setMbsex(rset.getString("mbsex"));
				result.setMbidentity(rset.getString("mbidentity"));
				result.setMbbirth(rset.getDate("mbbirth"));
				result.setMbnick(rset.getString("mbnick"));
				result.setMbemail(rset.getString("mbemail"));
				result.setMbtel(rset.getString("mbtel"));
				result.setMbaddr(rset.getString("mbaddr"));
				result.setMbaccount(rset.getString("mbaccount"));
				result.setMbpassword(rset.getBytes("mbpassword"));
				result.setMbimg(rset.getBytes("mbimg"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rset != null) {
				try {
					rset.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
		
	}
	public static void main(String[] args) {

//		MemberDAO dao = new MemberDAOJdbc();
		
		//insert
//		Member bean = new Member();
//		
//		File f = new File("src/com/emp/model/Tomcat.gif");
//		FileInputStream fis;
//		byte[] image = null;
//		try {
//		fis = new FileInputStream(f);
//		image = new byte[(int) f.length()];
//		fis.read(image);
//		} catch (FileNotFoundException e) {	
//		e.printStackTrace();
//		} catch (IOException e) {
//		e.printStackTrace();
//		}
//		
//		
//		bean.setMbaccount("three");
//		bean.setMbaddr("taiean taipei");
//		bean.setMbimg(image);
//		bean.setMbemail("three@iii.org");
//		bean.setMbname("blue");
//		bean.setMbpassword("A".getBytes());
//		dao.insertMember(bean);
//		System.out.println(bean);

		//select
//		Member bean1 = dao.findMember(1);
//		System.out.println(bean1.getMbname());
//		System.out.println(bean1.getMbsex());

		
		//update
//		File f1 = new File("src/com/emp/model/Tomcat1.gif");
//		FileInputStream fis1;
//		byte[] image1 = null;
//		try {
//		fis1 = new FileInputStream(f1);
//		image1 = new byte[(int) f1.length()];
//		fis1.read(image1);
//		} catch (FileNotFoundException e) {	
//		e.printStackTrace();
//		} catch (IOException e) {
//		e.printStackTrace();
//		}
		
		
//		dao.updateMember("second1", "M", "B123456789", new java.sql.Date(0), "sec","second@iii.org", "0987654321", "taipei", "bbb", "2222", image1, 2);

	}
}
